package com.central.apps.controller;

import com.central.app.common.util.DateUtils;
import com.central.app.service.HandlerService;
import com.central.common.web.Result;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//采购看板接口
@Log4j2
@RestController
@RequestMapping("/Procurepanel")
public class ProcurepanelController {
    @Autowired
    private HandlerService handlerService;
    //年采购分析，从采购订单主表取数据，去掉采购的接收退货成本
    @GetMapping("/anprocureamount")
    public Result anprocureamount(@RequestParam Map<String,Object>params) throws Exception{
        List<Map<String, Object>> bySql = new ArrayList<>();
        try {
            bySql=handlerService.findBySql("SELECT YEAR(createtime) as year,sum(toderount) as toderount from gq_po GROUP BY YEAR(createtime) ");
            return Result.succeed(bySql,"查询成功");
        }catch (Exception e){
            e.printStackTrace();
        }
        return  Result.succeed(bySql,"查询成功");
    }
    //统计采购订单主表订单总金额
//    @GetMapping("/po")
//    public void po(@RequestParam Map<String,Object>params) throws Exception {
//        List<Map<String, Object>> bySql = handlerService.findBySql("select * from gq_po");
//        if (CollectionUtils.isNotEmpty(bySql)){
//            for (int i=0;i<bySql.size();i++){
//                Map<String, Object> map = bySql.get(i);
//                String id = MapUtils.getString(map, "id");
//                BigDecimal bigDecimal = new BigDecimal("0");
//                List<Map<String, Object>> bySql1 = handlerService.findBySql("select * from gq_poline where poid='" + id + "'");
//                if (CollectionUtils.isNotEmpty(bySql1)){
//                    for (int j=0;j<bySql1.size();j++){
//                        Map<String, Object> map1 = bySql1.get(j);
//                        String linecost = MapUtils.getString(map1, "linecost");
//                        if (StringUtils.isEmpty(linecost)){
//                            linecost="0";
//                            BigDecimal bigDecimal1 = new BigDecimal(linecost);
//                            bigDecimal=bigDecimal.add(bigDecimal1);
//                        }else {
//                            BigDecimal bigDecimal1 = new BigDecimal(linecost);
//                            bigDecimal=bigDecimal.add(bigDecimal1);
//                        }
//
//                    }
//                }
//                handlerService.updateBySql("update gq_po set toderount="+bigDecimal+" where id='"+id+"'");
//            }
//        }
//    }
    //计算采购订单会总行退货金额
//        @GetMapping("/po")
//        public void po(@RequestParam Map<String,Object>params) throws Exception {
//        List<Map<String, Object>> bySql = handlerService.findBySql("select * from gq_po");
//        if (CollectionUtils.isNotEmpty(bySql)){
//            for (int i=0;i<bySql.size();i++){
//                Map<String, Object> map = bySql.get(i);
//                String id = MapUtils.getString(map, "id");
//                List<Map<String, Object>> bySql1 = handlerService.findBySql("select * from gq_posline where poid='" + id + "'");
//                if (CollectionUtils.isNotEmpty(bySql1)){
//                    for (int j=0;j<bySql1.size();j++){
//                        Map<String, Object> map1 = bySql1.get(j);
//                        String id1 = MapUtils.getString(map1, "id");
//                        String unitcost = MapUtils.getString(map1, "unitcost");
//                        BigDecimal bigDecimal = new BigDecimal(unitcost);
//                        String rejectedqty = MapUtils.getString(map1, "rejectedqty");
//                        BigDecimal bigDecimal2 = new BigDecimal(rejectedqty);
//                        BigDecimal bigDecimal1 = new BigDecimal("0");
//                        bigDecimal1=bigDecimal.multiply(bigDecimal1);
//                        handlerService.updateBySql("update gq_posline set reamount="+bigDecimal+" where id='"+id1+"'");
//                    }
//                }
//
//            }
//        }
//    }
    //月采购金额
    @GetMapping("/monpurchamount")
    public Result monpurchamount(@RequestParam Map<String,Object>params) throws Exception{
        List<Map<String, Object>> bySql = new ArrayList<>();
        try {
            int year = DateUtils.getCurrentYear();
            bySql=handlerService.findBySql("SELECT month(createtime) as  month,sum(toderount) as totalcost from gq_po where year(createtime)='"+year+"' GROUP BY month(createtime)");
            return Result.succeed(bySql,"查询成功");
        }catch (Exception e){
            e.printStackTrace();
        }
        return  Result.succeed(bySql,"查询成功");
    }
    //物料采购金额占比
    @GetMapping("/promaproamount")
    public Result promaproamount(@RequestParam Map<String, Object> params) throws Exception {
        List<Map<String, Object>> bySql = new ArrayList<>();
        try {
            bySql = handlerService.findBySql("SELECT b.maxdesc as `name` ,sum(a.totalcost-a.reamount) as `value` from gq_posline a left join gq_item b on a.itemnum=b.itemnum where b.maxdesc is not null GROUP BY b.maxdesc");
            return  Result.succeed(bySql,"查询成功");
        }catch (Exception e){
            e.printStackTrace();
        }
        return  Result.succeed(bySql,"查询成功");
    }
    //物料采购情况
    @GetMapping("/materprocuretion")
    public Result materprocuretion(@RequestParam Map<String, Object> params) throws Exception {
        List<Map<String, Object>> bySql = new ArrayList<>();
        try {
            bySql = handlerService.findBySql("SELECT \n" +
                    "    b.maxdesc, \n" +
                    "    SUM(a.orderqty) AS orderqty, \n" +
                    "    AVG(a.totalcost) AS avgCost, \n" +
                    "    SUM(a.totalcost) AS totalcost\n" +
                    "FROM \n" +
                    "    gq_posline a\n" +
                    "LEFT JOIN \n" +
                    "    gq_item b \n" +
                    "ON \n" +
                    "    a.itemnum = b.itemnum\n" +
                    "WHERE \n" +
                    "    NOT(b.maxdesc <=> NULL) AND b.maxdesc != ''\n" +
                    "GROUP BY \n" +
                    "    b.maxdesc\n" +
                    "ORDER BY \n" +
                    "    totalcost DESC");
            return  Result.succeed(bySql,"查询成功");
        }catch (Exception e){
            e.printStackTrace();
        }
        return  Result.succeed(bySql,"查询成功");
    }
    //供应商处采购金额占比
    @GetMapping("/proporcureasuppliers")
    public Result proporcureasuppliers(@RequestParam Map<String, Object> params) throws Exception {
        List<Map<String, Object>> bySql = new ArrayList<>();
        try {
            bySql = handlerService.findBySql("SELECT\n" +
                    "\tb.suppliername AS `name`,\n" +
                    "\tsum( a.totalcost - a.reamount ) AS `value` \n" +
                    "FROM\n" +
                    "\tgq_posline a\n" +
                    "\tleft join gq_po b on a.poid=b.id\n" +
                    "WHERE\n" +
                    "\tb.suppliername IS NOT NULL and b.suppliername!=''\n" +
                    "GROUP BY\n" +
                    "\tb.suppliername");
            return  Result.succeed(bySql,"查询成功");
        }catch (Exception e){
            e.printStackTrace();
        }
        return  Result.succeed(bySql,"查询成功");
    }
    //供应商采购情况
    @GetMapping("/suppprocuretion")
    public Result suppprocuretion(@RequestParam Map<String, Object> params) throws Exception {
        List<Map<String, Object>> bySql = new ArrayList<>();
        try {
            bySql = handlerService.findBySql("SELECT\n" +
                    "    b.suppliername AS `name`,\n" +
                    "    SUM(a.orderqty) AS orderqty,\n" +
                    "    AVG(a.totalcost) AS avgCost,\n" +
                    "    SUM(a.totalcost) AS totalcost\n" +
                    "FROM\n" +
                    "    gq_posline a\n" +
                    "    INNER JOIN gq_po b\n" +
                    "        ON a.poid = b.id\n" +
                    "WHERE\n" +
                    "    b.suppliername <> '' AND b.suppliername IS NOT NULL\n" +
                    "GROUP BY\n" +
                    "    b.suppliername\n" +
                    "ORDER BY\n" +
                    "    totalcost DESC");
            return  Result.succeed(bySql,"查询成功");
        }catch (Exception e){
            e.printStackTrace();
        }
        return  Result.succeed(bySql,"查询成功");
    }
    //物料采购分布散点图
    @GetMapping("/scaplmaprodistribution")
    public Result scaplmaprodistribution(@RequestParam Map<String, Object> params) throws Exception {
        List<Map<String, Object>> bySql = new ArrayList<>();

            bySql = handlerService.findBySql("\tSELECT \n" +
                    "    SUM(CASE WHEN totalcost <= 1000 THEN 1 ELSE 0 END) AS \"1000\",\n" +
                    "    SUM(CASE WHEN totalcost > 1000 AND totalcost <= 10000 THEN 1 ELSE 0 END) AS \"1万\",\n" +
                    "    SUM(CASE WHEN totalcost > 10000 AND totalcost <= 100000 THEN 1 ELSE 0 END) AS \"10万\",\n" +
                    "    SUM(CASE WHEN totalcost > 100000 AND totalcost <= 1000000 THEN 1 ELSE 0 END) AS \"100万\",\n" +
                    "    SUM(CASE WHEN totalcost > 1000000 AND totalcost <= 10000000 THEN 1 ELSE 0 END) AS \"1000万\",\n" +
                    "    SUM(CASE WHEN totalcost > 10000000  THEN 1 ELSE 0 END) AS \"1亿\"\n" +
                    "FROM gq_posline");
            Map<String,Object[]> map = new HashMap<>();
            if (CollectionUtils.isNotEmpty(bySql)){
                List<String> x = new ArrayList<>();
                List<String> y = new ArrayList<>();
               x.add("1000");
               x.add("1万");
               x.add("10万");
               x.add("100万");
               x.add("1000万");
               x.add("1亿");
               y.add(MapUtils.getString(bySql.get(0),"1000"));
                y.add(MapUtils.getString(bySql.get(0),"1万"));
                y.add(MapUtils.getString(bySql.get(0),"10万"));
                y.add(MapUtils.getString(bySql.get(0),"100万"));
                y.add(MapUtils.getString(bySql.get(0),"1000万"));
                y.add(MapUtils.getString(bySql.get(0),"1亿"));
                Object[] xArray = (Object[]) x.toArray();
                Object[] yArray = (Object[]) y.toArray();
                map.put("x",xArray);
                map.put("y",yArray);
            }

            return Result.succeedWith(map, 0, "查询成功");

    }
}
